﻿Imports System.Data.SqlClient
Public Class FrmTimkiemKiemke
    Dim tbl As DataTable
    Dim dbAdapter As SqlDataAdapter
    Dim cmd As SqlCommand
    Dim tennhap As String
    Private Sub NapPhong()
        'cmd = New SqlCommand(" select * from DMPhong", ob_cnn)
        'dbAdapter = New SqlDataAdapter(cmd)
        'tbl = New DataTable()
        'dbAdapter.Fill(tbl)
        'ComboBoxPhong.DisplayMember = "Tenphong"
        'ComboBoxPhong.ValueMember = "maPhongID"
        'ComboBoxPhong.DataSource = tbl
        'ob_cnn.Close()
    End Sub
    Private Sub NapNgan()
        'cmd = New SqlCommand(" select * from DMNgan", ob_cnn)
        'dbAdapter = New SqlDataAdapter(cmd)
        'tbl = New DataTable()
        'dbAdapter.Fill(tbl)
        'ComboBoxNgan.DisplayMember = "TenNgan"
        'ComboBoxNgan.ValueMember = "maNganID"
        'ComboBoxNgan.DataSource = tbl
        'ob_cnn.Close()
    End Sub
    Private Sub NapKe()
        'cmd = New SqlCommand(" select * from DMKe", ob_cnn)
        'dbAdapter = New SqlDataAdapter(cmd)
        'tbl = New DataTable()
        'dbAdapter.Fill(tbl)
        'ComboBoxKe.DisplayMember = "TenKe"
        'ComboBoxKe.ValueMember = "maKeID"
        'ComboBoxKe.DataSource = tbl
        'ob_cnn.Close()
    End Sub
    Private Sub NapKho()
        'cmd = New SqlCommand(" select * from DMKho", ob_cnn)
        'dbAdapter = New SqlDataAdapter(cmd)
        'tbl = New DataTable()
        'dbAdapter.Fill(tbl)
        'ComboBoxKho.DisplayMember = "TenKho"
        'ComboBoxKho.ValueMember = "maKhoID"
        'ComboBoxKho.DataSource = tbl
        'ob_cnn.Close()
    End Sub
    Private Sub Naphoso()
        cmd = New SqlCommand(" select * from DMHosokiemke", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHoso.DisplayMember = "Tenloaihoso"
        ComboBoxHoso.ValueMember = "MaloaiID"
        ComboBoxHoso.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapTaiLieuKK()
        cmd = New SqlCommand(" select * from DMTaiLieuKK", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxTaiLieuKK.DisplayMember = "TenTaiLieuKK"
        ComboBoxTaiLieuKK.ValueMember = "MaTaiLieuKK"
        ComboBoxTaiLieuKK.DataSource = tbl
        ob_cnn.Close()
    End Sub
    Private Sub NapDonViHuyen()
        cmd = New SqlCommand(" select * from TENHUYEN", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxHuyen.DisplayMember = "T_huyen"
        ComboBoxHuyen.ValueMember = "maH"
        ComboBoxHuyen.DataSource = tbl
        ob_cnn.Close()
    End Sub

    Private Sub FrmNhapKiemke_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        NapKho()
        NapKe()
        NapNgan()
        NapPhong()
        Naphoso()
        NapTaiLieuKK()
        NapDonViHuyen()



    End Sub

    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBoxHuyen.SelectedIndexChanged
        ' load ten xa
        cmd = New SqlCommand(" select * from TenXa where mahuyen = '" & ComboBoxHuyen.SelectedValue.ToString & "' ", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        ComboBoxXa.DisplayMember = "TenXa"
        ComboBoxXa.ValueMember = "maxa"
        ComboBoxXa.DataSource = tbl
        ob_cnn.Close()
    End Sub


    Private Sub ButtonThoat_Click(sender As Object, e As EventArgs) Handles ButtonThoat.Click
        Me.Close()
    End Sub
    Private Sub TimKiemTheoThongKeKiemKeCapTinh()
        cmd = New SqlCommand("SELECT hskk.NgayNhapKho, hskk.NamThucHien, dmtlkk.TenTaiLieuKK, dmhskk.Tenloaihoso, dvhc.TenCapDVHC, thbq.ThoiHanBaoQuan, kho.TenKho, ke.TenKe, ngan.TenNgan, phong.TenPhong, tinhtrang.TenTinhTrang, huyen.T_Huyen, xa.TenXa, hskk.MahosoID " &
                                "FROM HOSOKIEMKE hskk, DMTailieuKK dmtlkk, DMHosokiemke dmhskk, CAPDONVIHANHCHINH dvhc, DMThoiHanBaoQuan thbq, TENXA xa, TENHUYEN huyen, DMKho kho, DMKe ke, DMNgan ngan, DMPhong phong, DMTinhTrang tinhtrang " &
                                "WHERE hskk.CapDVHCID = dvhc.CapDVHCID and hskk.MaLoaiHSID = dmtlkk.MaTaiLieuKK AND hskk.TenQuyen = dmhskk.MaloaiID AND hskk.MaTinhTrangId = tinhtrang.MaTinhTrangID AND  hskk.MaDMThoiHanID = thbq.MaDMThoiHan And hskk.MaxaId = xa.MaXa And hskk.MaHuyenID = huyen.maH And hskk.MaKhoId = kho.maKhoID And hskk.MaKeId = ke.MaKeID AND hskk.MaPhongId = phong.MaPhongID and hskk.MaNganId = ngan.MaNganID " &
                                "AND hskk.TenQuyen = '" & ComboBoxHoso.SelectedValue & "' " &
                                "AND hskk.MaLoaiHSID = '" & ComboBoxTaiLieuKK.SelectedValue & "' " &
                                "AND hskk.CapDVHCID = 1" &
                                "AND hskk.NamThucHien LIKE '%" & TextBoxNamLap.Text & "%'", ob_cnn)
        '"AND hskk.NgayNhapKho >= '" & dtpTuNgay.Text & "' " &
        '"AND hskk.NgayNhapKho <= '" & dtpDenNgay.Text & "' 

        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridView_hienthi.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub TimKiemTheoThongKeKiemKeCapHuyen()
        cmd = New SqlCommand("SELECT hskk.NgayNhapKho, hskk.NamThucHien, dmtlkk.TenTaiLieuKK, dmhskk.Tenloaihoso, dvhc.TenCapDVHC, thbq.ThoiHanBaoQuan, kho.TenKho, ke.TenKe, ngan.TenNgan, phong.TenPhong, tinhtrang.TenTinhTrang, huyen.T_Huyen, xa.TenXa, hskk.MahosoID " &
                                "FROM HOSOKIEMKE hskk, DMTailieuKK dmtlkk, DMHosokiemke dmhskk, CAPDONVIHANHCHINH dvhc, DMThoiHanBaoQuan thbq, TENXA xa, TENHUYEN huyen, DMKho kho, DMKe ke, DMNgan ngan, DMPhong phong, DMTinhTrang tinhtrang " &
                                "WHERE hskk.CapDVHCID = dvhc.CapDVHCID and hskk.MaLoaiHSID = dmtlkk.MaTaiLieuKK AND hskk.TenQuyen = dmhskk.MaloaiID AND hskk.MaTinhTrangId = tinhtrang.MaTinhTrangID AND hskk.MaDMThoiHanID = thbq.MaDMThoiHan And hskk.MaxaId = xa.MaXa And hskk.MaHuyenID = huyen.maH And hskk.MaKhoId = kho.maKhoID And hskk.MaKeId = ke.MaKeID AND hskk.MaPhongId = phong.MaPhongID and hskk.MaNganId = ngan.MaNganID " &
                                "AND hskk.TenQuyen = '" & ComboBoxHoso.SelectedValue & "' " &
                                "AND hskk.MaLoaiHSID = '" & ComboBoxTaiLieuKK.SelectedValue & "' " &
                                "AND hskk.MaHuyenID = '" & ComboBoxHuyen.SelectedValue & "' " &
                                "AND hskk.CapDVHCID = 2" &
                                "AND hskk.NamThucHien LIKE '%" & TextBoxNamLap.Text & "%'", ob_cnn)
        '"AND hskk.NgayNhapKho >= '" & dtpTuNgay.Text & "' " &
        '"AND hskk.NgayNhapKho <= '" & dtpDenNgay.Text & "' "
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridView_hienthi.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub TimKiemTheoThongKeKiemKeCapXa()
        cmd = New SqlCommand("SELECT hskk.NgayNhapKho, hskk.NamThucHien, dmtlkk.TenTaiLieuKK, dmhskk.Tenloaihoso, dvhc.TenCapDVHC, thbq.ThoiHanBaoQuan, kho.TenKho, ke.TenKe, ngan.TenNgan, phong.TenPhong, tinhtrang.TenTinhTrang, huyen.T_Huyen, xa.TenXa, hskk.MahosoID " &
                                "FROM HOSOKIEMKE hskk, DMTailieuKK dmtlkk, DMHosokiemke dmhskk, CAPDONVIHANHCHINH dvhc, DMThoiHanBaoQuan thbq, TENXA xa, TENHUYEN huyen, DMKho kho, DMKe ke, DMNgan ngan, DMPhong phong, DMTinhTrang tinhtrang " &
                                "WHERE hskk.CapDVHCID = dvhc.CapDVHCID and hskk.MaLoaiHSID = dmtlkk.MaTaiLieuKK AND hskk.TenQuyen = dmhskk.MaloaiID AND hskk.MaTinhTrangId = tinhtrang.MaTinhTrangID AND hskk.MaDMThoiHanID = thbq.MaDMThoiHan And hskk.MaxaId = xa.MaXa And hskk.MaHuyenID = huyen.maH And hskk.MaKhoId = kho.maKhoID And hskk.MaKeId = ke.MaKeID AND hskk.MaPhongId = phong.MaPhongID and hskk.MaNganId = ngan.MaNganID " &
                                "AND hskk.TenQuyen = '" & ComboBoxHoso.SelectedValue & "' " &
                                "AND hskk.MaLoaiHSID = '" & ComboBoxTaiLieuKK.SelectedValue & "' " &
                                "AND hskk.MaHuyenID = '" & ComboBoxHuyen.SelectedValue & "' " &
                                "AND hskk.MaxaId = '" & ComboBoxXa.SelectedValue & "' " &
                                "AND hskk.CapDVHCID = 3" &
                                "AND hskk.NamThucHien LIKE '%" & TextBoxNamLap.Text & "%'", ob_cnn)
        dbAdapter = New SqlDataAdapter(cmd)
        tbl = New DataTable()
        dbAdapter.Fill(tbl)
        DataGridView_hienthi.DataSource = tbl
        If tbl.Rows.Count = 0 Then
            MessageBox.Show("Không tìm thấy thông tin ! ")
        End If
        ob_cnn.Close()
    End Sub
    Private Sub ButtonTimkiem_Click(sender As Object, e As EventArgs) Handles ButtonTimkiem.Click
        If rbTinh.Checked Then
            TimKiemTheoThongKeKiemKeCapTinh()
        End If
        If rbHuyen.Checked Then
            TimKiemTheoThongKeKiemKeCapHuyen()
        End If
        If rbXa.Checked Then
            TimKiemTheoThongKeKiemKeCapXa()
        End If
    End Sub

    Private Sub rbTinh_CheckedChanged(sender As Object, e As EventArgs) Handles rbTinh.CheckedChanged
        If rbTinh.Checked Then
            ComboBoxHuyen.Enabled = False
            ComboBoxXa.Enabled = False
        End If
    End Sub

    Private Sub rbHuyen_CheckedChanged(sender As Object, e As EventArgs) Handles rbHuyen.CheckedChanged
        If rbHuyen.Checked Then
            ComboBoxXa.Enabled = False
            ComboBoxHuyen.Enabled = True
        End If
    End Sub
    Private Sub rbXa_CheckedChanged(sender As Object, e As EventArgs) Handles rbXa.CheckedChanged
        If rbXa.Checked Then
            ComboBoxHuyen.Enabled = True
            ComboBoxXa.Enabled = True
        End If
    End Sub

    
    Private Sub ButtonXuatExcel_Click(sender As Object, e As EventArgs) Handles ButtonXuatExcel.Click
        XuatExcel(DataGridView_hienthi, "ThongKeKiemKe")
    End Sub
End Class